In the application of electronic payment transaction fraud detection, it is often requested by fraud detection software to count transaction volume or sum the total dollar amount over multiple time periods. The intent is to establish statistics of payment history of a payment receiver, a payment originator or a pair of originator and receiver in order to gauge the likelihood of the current payment transaction being fraudulent. For example, a sudden surge of high volume, small dollar amount payment transactions between a pair with no recent history indicates either a behavior change of payment originator or a hijacked account by a third party. The transactions in question should then be flagged for investigation.
Common fraud detection software relies on a relational database management system to store and manage the payment transaction records. Depending on the type of electronic payment, transaction records can be inserted into the database at any time individually or at fixed time intervals in batches. The fraud detection software then issues queries in Structured Query Language (SQL) to the underlying relational database to collect aggregated statistics about parties involved in the transaction.
The aggregated statistics queries must be answered quickly so that the fraud detection software can make a decision in a split second. In the presence of hundreds of millions of transactions in the database, query response times in sub-seconds can only be achieved through pre-computed results, also known as materialized tables. These materialized tables store aggregated values such as summation and counts of the raw database transaction records based on pre-defined queries issued by the fraud detection software. The efficiency and accuracy of the system depend on the selection of such materialized aggregate tables.